{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'套餐编码': {0: 'P0000183004',\n",
       "  1: 'P0000183805',\n",
       "  2: 'P0000183806',\n",
       "  3: 'P0000183966',\n",
       "  4: 'P0000087209',\n",
       "  5: 'P0000144304',\n",
       "  6: 'P0000183807',\n",
       "  7: 'P0000183808',\n",
       "  8: 'P0000183809'},\n",
       " '拆分后自动将同一套餐显示在一个同一个excel表格里（注意不是Sheet），文件名如下': {0: '国庆H5-北京大学-合同编号-XSHT2021090585616-套餐一-海鲜组合-备货编号LT0000088876-某某日期配送（这个日期为当前日期的后天日期）',\n",
       "  1: '国庆H5-北京大学-合同编号-XSHT2021090585616-套餐二-烧烤组合-备货编号LT0000088896-某某日期配送（这个日期为当前日期的后天日期）',\n",
       "  2: '国庆H5-北京大学-合同编号-XSHT2021090585616-套餐三-牛肉海鲜粮油组合-备货编号LT0000088897-某某日期配送（这个日期为当前日期的后天日期）',\n",
       "  3: '国庆H5-北京大学-合同编号-XSHT2021090585616-套餐四-米面粮油组合-备货编号LT0000088898-某某日期配送（这个日期为当前日期的后天日期）',\n",
       "  4: '国庆H5-北京大学-合同编号-XSHT2021090585616-套餐五-水果乳品组合-备货编号LT0000088901-某某日期配送（这个日期为当前日期的后天日期）',\n",
       "  5: '国庆H5-北京大学-合同编号-XSHT2021090585616-套餐六-干货组合-备货编号LT0000088902-某某日期配送（这个日期为当前日期的后天日期）',\n",
       "  6: '国庆H5-北京大学-合同编号-XSHT2021090585616-套餐七-升降火锅锅厨具7件套-备货编号LT0000088903-某某日期配送（这个日期为当前日期的后天日期）',\n",
       "  7: '国庆H5-北京大学-合同编号-XSHT2021090585616-套餐八-摩飞多功能锅-备货编号LT0000088903-某某日期配送（这个日期为当前日期的后天日期）',\n",
       "  8: '国庆H5-北京大学-合同编号-XSHT2021090585616-套餐九-美的(Midea)6段温控电热水壶-备货编号LT0000088903-某某日期配送（这个日期为当前日期的后天日期）'},\n",
       " '商品名称': {0: '三都港 速冻黄花鱼700g  2条/袋|正大生冻 白对虾300g/盒|百年渔港大连冷冻即食海参90g(4只)盒装|OCEAN SIR 法国银鳕鱼 200g(1-2片装)|鲜到鲜得带鱼整条1000g',\n",
       "  1: '西鲜记 盐池滩羊羔羊后腿1700g|西鲜记 盐池滩羊 羔羊肉串(12串)240g|国产安格斯牛腩块1000g|国产安格斯西冷牛排200g|国产筋头巴脑1000g',\n",
       "  2: '国产安格斯牛肉组合2400g|三都港 三去黄花鱼 1kg 2条/袋|正大生冻黑虎虾 240g|金龙鱼乳玉皇妃凝玉稻香贡米2500g|贝蒂斯特级初榨橄榄油（罐装）250ml',\n",
       "  3: '北纬45 原生稻稻花香大米 5000g|河套雪花粉2500g|贝蒂斯特级初榨橄榄油礼盒（1L*2）|百菇园 菌菇礼盒900g',\n",
       "  4: '佳沛新西兰阳光金果20粒|进口橙子礼盒4500g|蒙自百年古树福榴（石榴）3000g装|台湾麻豆老欉文旦柚3kg装(5-8个)|西麦有机燕麦片350g*3桶 礼盒|简爱 0%蔗糖椰子口味风味发酵乳135g*4',\n",
       "  5: '坚果365混合果仁25g*30袋(新)|养生堂母亲牛肉棒牛气冲天缤纷装266g|富昌万事如意亲民大礼包B款900g|参之源 南朝长白山人参100g礼盒',\n",
       "  6: '德国蓝宝升降火锅蓝色+摩飞多功能锅厨具7件套',\n",
       "  7: '摩飞多功能锅 MR9088(红)',\n",
       "  8: '美的(Midea)6段温控电热水壶MK-HE3001'},\n",
       " '商品编码': {0: 'P0000012909|P0000139130|P0000085492|P0000089652|P0000152055',\n",
       "  1: 'P0000113227|P0000102801|P0000146591|P0000152436|P0000145527',\n",
       "  2: 'P0000172964|P0000056957|P0000146802|P0000152624|P0000137256',\n",
       "  3: 'P0000175905|P0000014237|P0000082520|P0000098384',\n",
       "  4: 'P0000163624|P0000137658|P0000111647|P0000178128|P0000082653|P0000141184',\n",
       "  5: 'P0000147258|P0000160929|P0000181356|P0000158594',\n",
       "  6: 'P0000154750|P0000154403',\n",
       "  7: 'P0000102496',\n",
       "  8: 'P0000094934'},\n",
       " '商品价格': {0: '58.0|29.9|68.3|69.9|56.0',\n",
       "  1: '278.9|49.9|79.8|34.9|71.7',\n",
       "  2: '299|112|38|35|39',\n",
       "  3: '95.0|49.3|286.0|169.7',\n",
       "  4: '199.0|109.0|89.0|79.0|86.0|38.0',\n",
       "  5: '109.9|99.8|89.0|115.3',\n",
       "  6: '501.0|99.0',\n",
       "  7: 600,\n",
       "  8: 600},\n",
       " '订购数量': {0: '1|2|1|1|1',\n",
       "  1: '1|2|1|2|1',\n",
       "  2: '1|1|2|1|2',\n",
       "  3: '1|1|1|1',\n",
       "  4: '1|1|1|1|1|1',\n",
       "  5: '1|1|1|1',\n",
       "  6: '1|1',\n",
       "  7: 1,\n",
       "  8: 1}}"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#用于提取数据不要动\n",
    "# df = pd.read_excel(r'开发需求(1).xls',sheet_name='套餐对应数据')\n",
    "# df[['套餐编码',\n",
    "#     '拆分后自动将同一套餐显示在一个同一个excel表格里（注意不是Sheet），文件名如下',\n",
    "#     '商品名称',\n",
    "#     '商品编码',\n",
    "#     '商品价格',\t\n",
    "#     '订购数量'\n",
    "#     ]].dropna().to_dict()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 将提取的数据保存在这里不需要动这里\n",
    "dict1 = {'套餐编码': {0: 'P0000183004',\n",
    "  1: 'P0000183805',\n",
    "  2: 'P0000183806',\n",
    "  3: 'P0000183966',\n",
    "  4: 'P0000087209',\n",
    "  5: 'P0000144304',\n",
    "  6: 'P0000183807',\n",
    "  7: 'P0000183808',\n",
    "  8: 'P0000183809'},\n",
    " '拆分后自动将同一套餐显示在一个同一个excel表格里（注意不是Sheet），文件名如下': {0: '国庆H5-北京大学-合同编号-XSHT2021090585616-套餐一-海鲜组合-备货编号LT0000088876-某某日期配送（这个日期为当前日期的后天日期）',\n",
    "  1: '国庆H5-北京大学-合同编号-XSHT2021090585616-套餐二-烧烤组合-备货编号LT0000088896-某某日期配送（这个日期为当前日期的后天日期）',\n",
    "  2: '国庆H5-北京大学-合同编号-XSHT2021090585616-套餐三-牛肉海鲜粮油组合-备货编号LT0000088897-某某日期配送（这个日期为当前日期的后天日期）',\n",
    "  3: '国庆H5-北京大学-合同编号-XSHT2021090585616-套餐四-米面粮油组合-备货编号LT0000088898-某某日期配送（这个日期为当前日期的后天日期）',\n",
    "  4: '国庆H5-北京大学-合同编号-XSHT2021090585616-套餐五-水果乳品组合-备货编号LT0000088901-某某日期配送（这个日期为当前日期的后天日期）',\n",
    "  5: '国庆H5-北京大学-合同编号-XSHT2021090585616-套餐六-干货组合-备货编号LT0000088902-某某日期配送（这个日期为当前日期的后天日期）',\n",
    "  6: '国庆H5-北京大学-合同编号-XSHT2021090585616-套餐七-升降火锅锅厨具7件套-备货编号LT0000088903-某某日期配送（这个日期为当前日期的后天日期）',\n",
    "  7: '国庆H5-北京大学-合同编号-XSHT2021090585616-套餐八-摩飞多功能锅-备货编号LT0000088903-某某日期配送（这个日期为当前日期的后天日期）',\n",
    "  8: '国庆H5-北京大学-合同编号-XSHT2021090585616-套餐九-美的(Midea)6段温控电热水壶-备货编号LT0000088903-某某日期配送（这个日期为当前日期的后天日期）'},\n",
    " '商品名称': {0: '三都港 速冻黄花鱼700g  2条/袋|正大生冻 白对虾300g/盒|百年渔港大连冷冻即食海参90g(4只)盒装|OCEAN SIR 法国银鳕鱼 200g(1-2片装)|鲜到鲜得带鱼整条1000g',\n",
    "  1: '西鲜记 盐池滩羊羔羊后腿1700g|西鲜记 盐池滩羊 羔羊肉串(12串)240g|国产安格斯牛腩块1000g|国产安格斯西冷牛排200g|国产筋头巴脑1000g',\n",
    "  2: '国产安格斯牛肉组合2400g|三都港 三去黄花鱼 1kg 2条/袋|正大生冻黑虎虾 240g|金龙鱼乳玉皇妃凝玉稻香贡米2500g|贝蒂斯特级初榨橄榄油（罐装）250ml',\n",
    "  3: '北纬45 原生稻稻花香大米 5000g|河套雪花粉2500g|贝蒂斯特级初榨橄榄油礼盒（1L*2）|百菇园 菌菇礼盒900g',\n",
    "  4: '佳沛新西兰阳光金果20粒|进口橙子礼盒4500g|蒙自百年古树福榴（石榴）3000g装|台湾麻豆老欉文旦柚3kg装(5-8个)|西麦有机燕麦片350g*3桶 礼盒|简爱 0%蔗糖椰子口味风味发酵乳135g*4',\n",
    "  5: '坚果365混合果仁25g*30袋(新)|养生堂母亲牛肉棒牛气冲天缤纷装266g|富昌万事如意亲民大礼包B款900g|参之源 南朝长白山人参100g礼盒',\n",
    "  6: '德国蓝宝升降火锅蓝色+摩飞多功能锅厨具7件套',\n",
    "  7: '摩飞多功能锅 MR9088(红)',\n",
    "  8: '美的(Midea)6段温控电热水壶MK-HE3001'},\n",
    " '商品编码': {0: 'P0000012909|P0000139130|P0000085492|P0000089652|P0000152055',\n",
    "  1: 'P0000113227|P0000102801|P0000146591|P0000152436|P0000145527',\n",
    "  2: 'P0000172964|P0000056957|P0000146802|P0000152624|P0000137256',\n",
    "  3: 'P0000175905|P0000014237|P0000082520|P0000098384',\n",
    "  4: 'P0000163624|P0000137658|P0000111647|P0000178128|P0000082653|P0000141184',\n",
    "  5: 'P0000147258|P0000160929|P0000181356|P0000158594',\n",
    "  6: 'P0000154750|P0000154403',\n",
    "  7: 'P0000102496',\n",
    "  8: 'P0000094934'},\n",
    " '商品价格': {0: '58.0|29.9|68.3|69.9|56.0',\n",
    "  1: '278.9|49.9|79.8|34.9|71.7',\n",
    "  2: '299|112|38|35|39',\n",
    "  3: '95.0|49.3|286.0|169.7',\n",
    "  4: '199.0|109.0|89.0|79.0|86.0|38.0',\n",
    "  5: '109.9|99.8|89.0|115.3',\n",
    "  6: '501.0|99.0',\n",
    "  7: 600,\n",
    "  8: 600},\n",
    " '订购数量': {0: '1|2|1|1|1',\n",
    "  1: '1|2|1|2|1',\n",
    "  2: '1|1|2|1|2',\n",
    "  3: '1|1|1|1',\n",
    "  4: '1|1|1|1|1|1',\n",
    "  5: '1|1|1|1',\n",
    "  6: '1|1',\n",
    "  7: 1,\n",
    "  8: 1}}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>套餐编码</th>\n",
       "      <th>拆分后自动将同一套餐显示在一个同一个excel表格里（注意不是Sheet），文件名如下</th>\n",
       "      <th>商品名称</th>\n",
       "      <th>商品编码</th>\n",
       "      <th>商品价格</th>\n",
       "      <th>订购数量</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>P0000183004</td>\n",
       "      <td>国庆H5-北京大学-合同编号-XSHT2021090585616-套餐一-海鲜组合-备货编号...</td>\n",
       "      <td>三都港 速冻黄花鱼700g  2条/袋|正大生冻 白对虾300g/盒|百年渔港大连冷冻即食海...</td>\n",
       "      <td>P0000012909|P0000139130|P0000085492|P000008965...</td>\n",
       "      <td>58.0|29.9|68.3|69.9|56.0</td>\n",
       "      <td>1|2|1|1|1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>P0000183805</td>\n",
       "      <td>国庆H5-北京大学-合同编号-XSHT2021090585616-套餐二-烧烤组合-备货编号...</td>\n",
       "      <td>西鲜记 盐池滩羊羔羊后腿1700g|西鲜记 盐池滩羊 羔羊肉串(12串)240g|国产安格斯...</td>\n",
       "      <td>P0000113227|P0000102801|P0000146591|P000015243...</td>\n",
       "      <td>278.9|49.9|79.8|34.9|71.7</td>\n",
       "      <td>1|2|1|2|1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>P0000183806</td>\n",
       "      <td>国庆H5-北京大学-合同编号-XSHT2021090585616-套餐三-牛肉海鲜粮油组合-...</td>\n",
       "      <td>国产安格斯牛肉组合2400g|三都港 三去黄花鱼 1kg 2条/袋|正大生冻黑虎虾 240g...</td>\n",
       "      <td>P0000172964|P0000056957|P0000146802|P000015262...</td>\n",
       "      <td>299|112|38|35|39</td>\n",
       "      <td>1|1|2|1|2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>P0000183966</td>\n",
       "      <td>国庆H5-北京大学-合同编号-XSHT2021090585616-套餐四-米面粮油组合-备货...</td>\n",
       "      <td>北纬45 原生稻稻花香大米 5000g|河套雪花粉2500g|贝蒂斯特级初榨橄榄油礼盒（1L...</td>\n",
       "      <td>P0000175905|P0000014237|P0000082520|P0000098384</td>\n",
       "      <td>95.0|49.3|286.0|169.7</td>\n",
       "      <td>1|1|1|1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>P0000087209</td>\n",
       "      <td>国庆H5-北京大学-合同编号-XSHT2021090585616-套餐五-水果乳品组合-备货...</td>\n",
       "      <td>佳沛新西兰阳光金果20粒|进口橙子礼盒4500g|蒙自百年古树福榴（石榴）3000g装|台湾...</td>\n",
       "      <td>P0000163624|P0000137658|P0000111647|P000017812...</td>\n",
       "      <td>199.0|109.0|89.0|79.0|86.0|38.0</td>\n",
       "      <td>1|1|1|1|1|1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>P0000144304</td>\n",
       "      <td>国庆H5-北京大学-合同编号-XSHT2021090585616-套餐六-干货组合-备货编号...</td>\n",
       "      <td>坚果365混合果仁25g*30袋(新)|养生堂母亲牛肉棒牛气冲天缤纷装266g|富昌万事如意...</td>\n",
       "      <td>P0000147258|P0000160929|P0000181356|P0000158594</td>\n",
       "      <td>109.9|99.8|89.0|115.3</td>\n",
       "      <td>1|1|1|1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>P0000183807</td>\n",
       "      <td>国庆H5-北京大学-合同编号-XSHT2021090585616-套餐七-升降火锅锅厨具7件...</td>\n",
       "      <td>德国蓝宝升降火锅蓝色+摩飞多功能锅厨具7件套</td>\n",
       "      <td>P0000154750|P0000154403</td>\n",
       "      <td>501.0|99.0</td>\n",
       "      <td>1|1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>P0000183808</td>\n",
       "      <td>国庆H5-北京大学-合同编号-XSHT2021090585616-套餐八-摩飞多功能锅-备货...</td>\n",
       "      <td>摩飞多功能锅 MR9088(红)</td>\n",
       "      <td>P0000102496</td>\n",
       "      <td>600</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>P0000183809</td>\n",
       "      <td>国庆H5-北京大学-合同编号-XSHT2021090585616-套餐九-美的(Midea)...</td>\n",
       "      <td>美的(Midea)6段温控电热水壶MK-HE3001</td>\n",
       "      <td>P0000094934</td>\n",
       "      <td>600</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          套餐编码        拆分后自动将同一套餐显示在一个同一个excel表格里（注意不是Sheet），文件名如下  \\\n",
       "0  P0000183004  国庆H5-北京大学-合同编号-XSHT2021090585616-套餐一-海鲜组合-备货编号...   \n",
       "1  P0000183805  国庆H5-北京大学-合同编号-XSHT2021090585616-套餐二-烧烤组合-备货编号...   \n",
       "2  P0000183806  国庆H5-北京大学-合同编号-XSHT2021090585616-套餐三-牛肉海鲜粮油组合-...   \n",
       "3  P0000183966  国庆H5-北京大学-合同编号-XSHT2021090585616-套餐四-米面粮油组合-备货...   \n",
       "4  P0000087209  国庆H5-北京大学-合同编号-XSHT2021090585616-套餐五-水果乳品组合-备货...   \n",
       "5  P0000144304  国庆H5-北京大学-合同编号-XSHT2021090585616-套餐六-干货组合-备货编号...   \n",
       "6  P0000183807  国庆H5-北京大学-合同编号-XSHT2021090585616-套餐七-升降火锅锅厨具7件...   \n",
       "7  P0000183808  国庆H5-北京大学-合同编号-XSHT2021090585616-套餐八-摩飞多功能锅-备货...   \n",
       "8  P0000183809  国庆H5-北京大学-合同编号-XSHT2021090585616-套餐九-美的(Midea)...   \n",
       "\n",
       "                                                商品名称  \\\n",
       "0  三都港 速冻黄花鱼700g  2条/袋|正大生冻 白对虾300g/盒|百年渔港大连冷冻即食海...   \n",
       "1  西鲜记 盐池滩羊羔羊后腿1700g|西鲜记 盐池滩羊 羔羊肉串(12串)240g|国产安格斯...   \n",
       "2  国产安格斯牛肉组合2400g|三都港 三去黄花鱼 1kg 2条/袋|正大生冻黑虎虾 240g...   \n",
       "3  北纬45 原生稻稻花香大米 5000g|河套雪花粉2500g|贝蒂斯特级初榨橄榄油礼盒（1L...   \n",
       "4  佳沛新西兰阳光金果20粒|进口橙子礼盒4500g|蒙自百年古树福榴（石榴）3000g装|台湾...   \n",
       "5  坚果365混合果仁25g*30袋(新)|养生堂母亲牛肉棒牛气冲天缤纷装266g|富昌万事如意...   \n",
       "6                             德国蓝宝升降火锅蓝色+摩飞多功能锅厨具7件套   \n",
       "7                                   摩飞多功能锅 MR9088(红)   \n",
       "8                         美的(Midea)6段温控电热水壶MK-HE3001   \n",
       "\n",
       "                                                商品编码  \\\n",
       "0  P0000012909|P0000139130|P0000085492|P000008965...   \n",
       "1  P0000113227|P0000102801|P0000146591|P000015243...   \n",
       "2  P0000172964|P0000056957|P0000146802|P000015262...   \n",
       "3    P0000175905|P0000014237|P0000082520|P0000098384   \n",
       "4  P0000163624|P0000137658|P0000111647|P000017812...   \n",
       "5    P0000147258|P0000160929|P0000181356|P0000158594   \n",
       "6                            P0000154750|P0000154403   \n",
       "7                                        P0000102496   \n",
       "8                                        P0000094934   \n",
       "\n",
       "                              商品价格         订购数量  \n",
       "0         58.0|29.9|68.3|69.9|56.0    1|2|1|1|1  \n",
       "1        278.9|49.9|79.8|34.9|71.7    1|2|1|2|1  \n",
       "2                 299|112|38|35|39    1|1|2|1|2  \n",
       "3            95.0|49.3|286.0|169.7      1|1|1|1  \n",
       "4  199.0|109.0|89.0|79.0|86.0|38.0  1|1|1|1|1|1  \n",
       "5            109.9|99.8|89.0|115.3      1|1|1|1  \n",
       "6                       501.0|99.0          1|1  \n",
       "7                              600            1  \n",
       "8                              600            1  "
      ]
     },
     "execution_count": 104,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_corr_data = pd.DataFrame(dict1)\n",
    "df_corr_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# source_data_path = input(r'请输入你要处理的文件路径：') # 你用的时候把这里的注释打开，以供你输入指定对应的地址，我这里为了测试直接使用我的本地路径了\n",
    "import xlwings as xw\n",
    "from pathlib import Path\n",
    "import pandas as pd\n",
    "source_data_path = Path(r'D:\\Working\\私活\\拆分\\开发需求.xls')\n",
    "app = xw.App(visible=False,add_book=False)\n",
    "workbook = app.books.open(source_data_path)\n",
    "df_source = workbook.sheets[0].range('A1').expand('table').options(pd.DataFrame).value\n",
    "workbook.close()\n",
    "app.quit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>用户-密码</th>\n",
       "      <th>订单流水号</th>\n",
       "      <th>下单时间</th>\n",
       "      <th>收货人</th>\n",
       "      <th>收货电话</th>\n",
       "      <th>省份</th>\n",
       "      <th>城市</th>\n",
       "      <th>区域</th>\n",
       "      <th>收货地址</th>\n",
       "      <th>商品名称</th>\n",
       "      <th>商品编码</th>\n",
       "      <th>组织站点</th>\n",
       "      <th>业务线</th>\n",
       "      <th>销售渠道</th>\n",
       "      <th>商品价格</th>\n",
       "      <th>订购数量</th>\n",
       "      <th>订单-选择送达日期</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>用户-编号</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>00518754</th>\n",
       "      <td>00032250</td>\n",
       "      <td>687867</td>\n",
       "      <td>2021-09-23 14:12:58</td>\n",
       "      <td>张先生1</td>\n",
       "      <td>1.380014e+10</td>\n",
       "      <td>北京市</td>\n",
       "      <td>北京市</td>\n",
       "      <td>昌平区</td>\n",
       "      <td>北京大学职工家属楼001</td>\n",
       "      <td>套餐一 海鲜组合</td>\n",
       "      <td>P0000183004</td>\n",
       "      <td>华北</td>\n",
       "      <td>B2B</td>\n",
       "      <td>集采渠道</td>\n",
       "      <td>600.00</td>\n",
       "      <td>1</td>\n",
       "      <td>2021-09-26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>00518755</th>\n",
       "      <td>00032251</td>\n",
       "      <td>687935</td>\n",
       "      <td>2021-09-23 14:47:58</td>\n",
       "      <td>张先生2</td>\n",
       "      <td>1.380014e+10</td>\n",
       "      <td>北京市</td>\n",
       "      <td>北京市</td>\n",
       "      <td>海淀区</td>\n",
       "      <td>北京大学职工家属楼002</td>\n",
       "      <td>套餐八 摩飞多功能锅 MR9088(红)</td>\n",
       "      <td>P0000183808</td>\n",
       "      <td>华北</td>\n",
       "      <td>B2B</td>\n",
       "      <td>集采渠道</td>\n",
       "      <td>600.00</td>\n",
       "      <td>1</td>\n",
       "      <td>2021-09-26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>00518756</th>\n",
       "      <td>00032252</td>\n",
       "      <td>687942</td>\n",
       "      <td>2021-09-23 14:49:12</td>\n",
       "      <td>张先生3</td>\n",
       "      <td>1.380014e+10</td>\n",
       "      <td>北京市</td>\n",
       "      <td>北京市</td>\n",
       "      <td>海淀区</td>\n",
       "      <td>北京大学职工家属楼003</td>\n",
       "      <td>套餐九 美的(Midea)6段温控电热水壶MK-HE3001</td>\n",
       "      <td>P0000183809</td>\n",
       "      <td>华北</td>\n",
       "      <td>B2B</td>\n",
       "      <td>集采渠道</td>\n",
       "      <td>600.00</td>\n",
       "      <td>1</td>\n",
       "      <td>2021-09-26</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             用户-密码   订单流水号                 下单时间   收货人          收货电话   省份   城市  \\\n",
       "用户-编号                                                                           \n",
       "00518754  00032250  687867  2021-09-23 14:12:58  张先生1  1.380014e+10  北京市  北京市   \n",
       "00518755  00032251  687935  2021-09-23 14:47:58  张先生2  1.380014e+10  北京市  北京市   \n",
       "00518756  00032252  687942  2021-09-23 14:49:12  张先生3  1.380014e+10  北京市  北京市   \n",
       "\n",
       "           区域          收货地址                            商品名称         商品编码 组织站点  \\\n",
       "用户-编号                                                                           \n",
       "00518754  昌平区  北京大学职工家属楼001                        套餐一 海鲜组合  P0000183004   华北   \n",
       "00518755  海淀区  北京大学职工家属楼002            套餐八 摩飞多功能锅 MR9088(红)  P0000183808   华北   \n",
       "00518756  海淀区  北京大学职工家属楼003  套餐九 美的(Midea)6段温控电热水壶MK-HE3001  P0000183809   华北   \n",
       "\n",
       "          业务线  销售渠道    商品价格 订购数量  订单-选择送达日期  \n",
       "用户-编号                                        \n",
       "00518754  B2B  集采渠道  600.00    1 2021-09-26  \n",
       "00518755  B2B  集采渠道  600.00    1 2021-09-26  \n",
       "00518756  B2B  集采渠道  600.00    1 2021-09-26  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_source.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import datetime\n",
    "import pandas as pd\n",
    "import xlwings as xw\n",
    "sample_no = set(df_source['商品编码'].values)\n",
    "reference_no = df_corr_data['套餐编码'].values\n",
    "os.makedirs(r'./result/',exist_ok=True)\n",
    "date = datetime.date.today()+datetime.timedelta(days=2)\n",
    "date = date.__format__('%Y年%m月%d日')\n",
    "for i in sample_no:\n",
    "    if i in reference_no:\n",
    "        file_name = df_corr_data[df_corr_data['套餐编码']==i]['拆分后自动将同一套餐显示在一个同一个excel表格里（注意不是Sheet），文件名如下'].str.replace('某某日期配送（这个日期为当前日期的后天日期）',date+\"配送\")\n",
    "        file_path = './result/' + str(file_name.values[-1])+'.xls'\n",
    "        wb = pd.ExcelWriter(file_path)\n",
    "        res = df_source[df_source['商品编码']==i]\n",
    "        res\n",
    "        refer = df_corr_data[df_corr_data['套餐编码']==i]\n",
    "        res.loc[:,'商品名称'] = refer.loc[:,'商品名称'].values[-1]\n",
    "        res.loc[:,'商品编码'] = refer.loc[:,'商品编码'].values[-1]\n",
    "        res.loc[:,'商品价格'] = refer.loc[:,'商品价格'].values[-1]\n",
    "        res.loc[:,'订购数量'] = refer.loc[:,'订购数量'].values[-1]\n",
    "        res.to_excel(wb)\n",
    "        wb.save()\n",
    "    else:\n",
    "        print('文件选择错误')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "6f8ee94c255eb1f45edb80e83721093c1db1e2ea85447c0854292673b957abb8"
  },
  "kernelspec": {
   "display_name": "Python 3.8.5 64-bit ('base': conda)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
